Interview Questions

1. To roll back to some particular point in a transaction we use

Options:

a. Rollback tran

b. Begin tran

c. Rollback tran <save point name>

d. committed

Reveal Answer

2. By default the sql server is in _____________ isolation

Options:

a. Read uncommitted

b. Repeatable read

c. Serializable

d. Read committed

Reveal Answer

3. In a transaction if we get different values for a select statement which retrieves the same row then that concurrency problem is called

Options:

a. Dirty read

b. Non repeatable read

c. Phantom read

d. None

Reveal Answer

4. Which concurrency model does snapshot use?

Options:

a. Optimistic

b. Pessimistic

Reveal Answer

5. session-1: settransactionisolationlevelsnapshot begintran selectfromtrandemo selectfromtrandemo rollbacktran session-2: begintran updatetrandemosetvalue=200 committran From the above example, state which statement is true

Options:

a. The user in session-2 is locked until all the select statements are executed in session-1

b. The user in session-2 does not have to wait until all the select statements in session-2

c. User in session-2 goes into continuous wait even after the user in session-2 releases the lock

d. Rollback cannot be used in explicit transactions

Reveal Answer

6. When the isolation level is SNAPSHOT, the qualified records are in ___________

Options:

a. Master databases

b. Model databases

c. Tempdb database

d. Msdb database

Reveal Answer

7. Which has the lowest isolation level

Options:

a. Read committed

b. Read uncommited

c. Serializable

d. Snapshot

Reveal Answer

8. Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb.

Options:

a. TRUE

b. FALSE

Reveal Answer

9. Which has the highest isolation level

Options:

a. Snapshot

b. Repeatable read

c. Serializable

d. None

Reveal Answer

10. A table called salary is created with a single row inserted createtablesalary (salintcheck(sal>1000) ) insertintosalaryvalues(3500) Begin a Transasction: begintran begintry updateemployeesetsalary=salary+3000 wherepkempno=1 updatesalarysetsal=sal-3000 commit endtry begincatch rollback endcatch

Options:

a. Both the update statements get executed successfully

b. First update statement is executed successfully and is committed,but the second update statement fails.

c. Both update statements get executed successfully but are rolled back

d. First update statement is executed successfully and the second update statement fails, but both statements are rolled back

Reveal Answer

Interview Questions and Answer

1. To roll back to some particular point in a transaction we use

2. By default the sql server is in _____________ isolation

3. In a transaction if we get different values for a select statement which retrieves the same row then that concurrency problem is called

4. Which concurrency model does snapshot use?

5. session-1: settransactionisolationlevelsnapshot begintran selectfromtrandemo selectfromtrandemo rollbacktran session-2: begintran updatetrandemosetvalue=200 committran From the above example, state which statement is true

6. When the isolation level is SNAPSHOT, the qualified records are in ___________

7. Which has the lowest isolation level

8. Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb.

9. Which has the highest isolation level

10. A table called salary is created with a single row inserted createtablesalary (salintcheck(sal>1000) ) insertintosalaryvalues(3500) Begin a Transasction: begintran begintry updateemployeesetsalary=salary+3000 wherepkempno=1 updatesalarysetsal=sal-3000 commit endtry begincatch rollback endcatch

Packages

Trending Certification Courses:

Microsoft Mock Exams:

Other links

About

Resources